﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using System.Windows.Forms;

namespace sqlDataset
{
    
    public class Class1:IDisposable
    {
        SqlConnection cna = new SqlConnection(Maticsoft.DBUtility.Class1.cn);
        public DataTable dt = new DataTable();       
        SqlDataAdapter da;
        //获取分类datatable
        public DataTable adt(string sql)
        {
            da = new SqlDataAdapter(sql, cna);          
            da.Fill(dt);
            return dt;
        }
        public DataSet ads(string sql)
        {
            DataSet ds = new DataSet();
            da = new SqlDataAdapter(sql, cna);
            da.Fill(ds);
            return ds;
        }
        public DataTable adtbuilder(string sql)
        {
            da = new SqlDataAdapter(sql, cna);
            SqlCommandBuilder thisbuilder = new SqlCommandBuilder(da);
            da.Fill(dt);
            return dt;
        }
        public void updatebulider(DataTable dta)//更新数据源，即sqlserver 
        {
            da.Update(dta);
            dta.AcceptChanges();
        }
        public void cleardatabinding(Form fr)
        {
            foreach (Control ct in fr.Controls)
            {
                ct.DataBindings.Clear();                
            }
        }
        public void cleardt()
        {
            dt.Clear();
        }
        public string maxnostr(string tastr, string costr, string strformat)//tastr代表表名,constr代表字段名 strformat为 "00000"的形式,为格式
        {
            int lint = strformat.Length;
            string maxsql = "select isnull(substring(max("+costr+"),2,"+lint+"),'0') from "+tastr;
            string max2 = Maticsoft.DBUtility.Class1.cmdscalar(maxsql);
            //if (max2.Length < 1)
            //    max2 = "0";
            int maintt = Convert.ToInt32(max2) + 1;
            string rstr = maintt.ToString(strformat);
            return rstr;
        }
        public void SaveAsexcel(DataGridView dgvAgeWeekSex) //另存新档按钮  导出成Excel 
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

            saveFileDialog.FilterIndex = 0;

            saveFileDialog.RestoreDirectory = true;

            saveFileDialog.CreatePrompt = true;

            saveFileDialog.Title = "Export Excel File To";
            //如果用户点了取消,就不执行后面的代码,
            if (saveFileDialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            Stream myStream;
            myStream = saveFileDialog.OpenFile();//这里要考虑如果用户 取消保存的情况 ,不然会出错的.
            //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312")); 
            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
            string str = "";
            try
            {

                //写标题 

                int rowCount = dgvAgeWeekSex.RowCount;
                int columnCount = dgvAgeWeekSex.ColumnCount;

                for (int i = 0; i < columnCount; i++)
                {

                    if (i > 0)
                    {

                        str += "\t";

                    }

                    str += dgvAgeWeekSex.Columns[i].HeaderText;

                }

                sw.WriteLine(str);


                //写内容 

                for (int j = 0; j < rowCount; j++)
                {

                    string tempStr = "";

                    for (int k = 0; k < columnCount; k++)
                    {

                        if (k > 0)
                        {

                            tempStr += "\t";  // \t 水平制表符,占一个字符,相当于空格一样的东西

                        }
                        tempStr += dgvAgeWeekSex.Rows[j].Cells[k].FormattedValue.ToString();
                    }



                    sw.WriteLine(tempStr.Replace("\r\n", ""));

                }

                sw.Close();

                myStream.Close();

            }

            catch (Exception e)
            {

                MessageBox.Show(e.ToString());

            }

            finally
            {

                sw.Close();

                myStream.Close();

            }


        }
        public  void SaveAsdata(DataTable dgvAgeWeekSex) //另存新档按钮  导出成Excel ,用流的方式,用dataset 来导出
        {

            SaveFileDialog saveFileDialog = new SaveFileDialog();

            saveFileDialog.Filter = "Execl files (*.xls)|*.xls";

            saveFileDialog.FilterIndex = 0;

            saveFileDialog.RestoreDirectory = true;

            saveFileDialog.CreatePrompt = true;

            saveFileDialog.Title = "Export Excel File To";
            //如果用户点了取消,就不执行后面的代码,

            if (saveFileDialog.ShowDialog() == DialogResult.Cancel)
            {
                return;
            }

            Stream myStream;
            myStream = saveFileDialog.OpenFile();

            //StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("GB2312"));

            StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));

            string str = "";

            try
            {

                //写标题 

                int rowCount = dgvAgeWeekSex.Rows.Count;
                int columnCount = dgvAgeWeekSex.Columns.Count;

                for (int i = 0; i < columnCount; i++)
                {

                    if (i > 0)
                    {

                        str += "\t";

                    }

                    str += dgvAgeWeekSex.Columns[i].ColumnName;

                }


                sw.WriteLine(str);


                //写内容 

                for (int j = 0; j < rowCount; j++)
                {

                    string tempStr = "";

                    for (int k = 0; k < columnCount; k++)
                    {

                        if (k > 0)
                        {

                            tempStr += "\t";  // \t 水平制表符,占一个字符,相当于空格一样的东西

                        }
                        tempStr += dgvAgeWeekSex.Rows[j][k].ToString();


                    }

                    sw.WriteLine(tempStr.Replace("\r\n", ""));

                }

                sw.Close();

                myStream.Close();

            }

            catch (Exception e)
            {

                MessageBox.Show(e.ToString());

            }

            finally
            {

                sw.Close();

                myStream.Close();

            }

        }
        public void Dispose()
        {
            dt.Clear();
        }        
    }
}
